package clean

import (
	"github.com/360EntSecGroup-Skylar/excelize"
	"fmt"
	"gopkg.in/fatih/set.v0"
	"strings"
	"encoding/hex"
	"crypto/md5"
	//"github.com/tealeg/xlsx"
	"strconv"
	//"github.com/tealeg/xlsx"
)

type ClassItem struct {
	DepartmentCode string
	ClassCode string
	//SchoolName string
	ClassName string
	Total int32
	Men int32
	Women int32
	GradeCode string
	//GradeClassCount int32
	StudentType string
	SchoolName string
}

// 中职学校列表
var secondaryVocationalSchoolMap =  map[string]bool{
	"济南艺术学校": true,
	"山东省青岛卫生学校": true,
	"淄博信息工程学校（淄博市张店第一职业中等专业学校）": true,
	"东营市河口区职业中等专业学校": true,
	"枣庄市薛城区职业中专": true,
	"临沂科技普通中等专业学校": true,
	"莱芜市莱城区凤城高级中学": true,
	"山东水利职业学院": true,
	"文登市职业中等专业学校": true,
	"烟台工贸学校": true,
	"山东省潍坊商业学校": true,
	"山东省济宁卫生学校": true,
	"宁阳县职业中等专业学校": true,
	"惠民县卫生学校": true,
	"菏泽信息工程学校": true,
	"聊城工业学校": true,
	"禹城市职业教育中心": true,
}

// 高职学校列表
var HigherVocationalSchoolMap =  map[string]bool{
	"山东城市建设职业学院": true,
	"山东外贸职业学院": true,
	"山东轻工职业学院": true,
	"东营科技职业学院": true,
	"枣庄职业学院": true,
	"山东医学高等专科学校": true,
	"山东水利职业学院": true,
	"山东药品食品职业学院": true,
	"山东科技职业学院": true,
	"山东理工职业学院": true,
	"泰山护理职业学院": true,
	"滨州职业学院": true,
	"菏泽职业学院": true,
	"聊城职业技术学院": true,
	"德州科技职业学院": true,

}
func ExtractClasses(inFileList []string, outFileName string) {
	studentCodeSet := set.New()
	classMap := make(map[string]*ClassItem, 0)

	gradeClassCountMap := make(map[string]*set.Set,0)

	// 打开所有的excel
	xlsxList := make([]*excelize.File, 0)
	for _, fileName := range inFileList {
		temp, err := excelize.OpenFile(fileName)
		if err != nil {
			panic(err.Error())
		}
		xlsxList = append(xlsxList, temp)
	}
	for _, xlsxFile := range xlsxList {

		rows := xlsxFile.GetRows("Sheet1")
		for i, row := range rows {
			// 跳过表头
			if i == 0 {
				continue
			}
			studentCode := CleanField(row[5])
			fmt.Println(studentCode)

			// 重复了
			if studentCodeSet.Has(studentCode) {
				continue
			}
			studentCodeSet.Add(studentCode)
			// 生成class code
			classCode := GetMD5Hash(CleanField(row[2] + row[4]))
			if _, ok := classMap[classCode]; ok {

			} else {
				temp := &ClassItem{}
				temp.ClassCode = classCode
				temp.ClassName = CleanField(row[4])
				temp.DepartmentCode = CleanField(row[1])
				temp.GradeCode = CleanField(row[3])
				temp.SchoolName = CleanField(row[2])
				temp.StudentType = GetStudentType(temp.GradeCode, temp.SchoolName)
				classMap[classCode] = temp
			}
			// 男女生
			item, _ := classMap[classCode]
			item.Total += 1
			if CleanField(row[8]) == "1" {
				item.Men += 1
			} else if CleanField(row[8]) == "2" {
				item.Women += 1
			}
			// 统计同一学校同一年级有多少班级
			gradeClass := CleanField(row[2] + row[3])
			if _, ok := gradeClassCountMap[gradeClass]; ok {

			} else {
				gradeClassCountMap[gradeClass] = set.New()
			}
			gradeClassCountMap[gradeClass].Add(classCode)
			//var item :=
			//classSet.Add(className)
			//fmt.Println(row[7]);

		}
	}
	xlsxFile := excelize.NewFile()
	//index := xlsxFile.NewSheet("Sheet1")
	xlsxFile.SetCellValue("Sheet1", "A1", "序号")
	xlsxFile.SetCellValue("Sheet1", "B1", "所属组织机构代码")
	xlsxFile.SetCellValue("Sheet1", "C1", "班级编号（唯一）")
	xlsxFile.SetCellValue("Sheet1", "D1", "班级名称")
	xlsxFile.SetCellValue("Sheet1", "E1", "班级学生总数")
	xlsxFile.SetCellValue("Sheet1", "F1", "班级男生数")
	xlsxFile.SetCellValue("Sheet1", "G1", "班级女生数")
	xlsxFile.SetCellValue("Sheet1", "H1", "年级编号（直接填年级代码）")
	xlsxFile.SetCellValue("Sheet1", "I1", "年级班级总数")
	xlsxFile.SetCellValue("Sheet1", "J1", "学生类型")
	var classindex int64 = 0
	for _, value := range classMap {
		classindex += 1
		// Create a new sheet.
		// Set value of a cell.
		//xlsx.SetCellValue("Sheet2", "A2", "Hello world.")
		//xlsx.SetCellValue("Sheet1", "B2", 100)
		// Set active sheet of the workbook.
		// Save xlsx file by the given path.
		fmt.Println(classindex)
		gradeClassCount := gradeClassCountMap[value.SchoolName + value.GradeCode].Size()
		xlsxFile.SetCellValue("Sheet1", "A" + strconv.FormatInt(classindex + 1, 10), strconv.FormatInt(classindex, 10))
		xlsxFile.SetCellValue("Sheet1", "B" + strconv.FormatInt(classindex + 1, 10), value.DepartmentCode)
		xlsxFile.SetCellValue("Sheet1", "C" + strconv.FormatInt(classindex + 1, 10), value.ClassCode)
		xlsxFile.SetCellValue("Sheet1", "D" + strconv.FormatInt(classindex + 1, 10), value.ClassName)
		xlsxFile.SetCellValue("Sheet1", "E" + strconv.FormatInt(classindex + 1, 10), value.Total)
		xlsxFile.SetCellValue("Sheet1", "F" + strconv.FormatInt(classindex + 1, 10), value.Men)
		xlsxFile.SetCellValue("Sheet1", "G" + strconv.FormatInt(classindex + 1, 10), value.Women)
		xlsxFile.SetCellValue("Sheet1", "H" + strconv.FormatInt(classindex + 1, 10), value.GradeCode)
		xlsxFile.SetCellValue("Sheet1", "I" + strconv.FormatInt(classindex + 1, 10), strconv.FormatInt(int64(gradeClassCount), 10))
		xlsxFile.SetCellValue("Sheet1", "J" + strconv.FormatInt(classindex + 1, 10), value.StudentType)

	}
	err := xlsxFile.SaveAs(outFileName)
	if err != nil {
		fmt.Println(err)
	}
	//xlsx.SetActiveSheet(index)

}

//去掉空格，逗号改为点
func CleanField(in string) string {
	result := strings.Replace(in, " ", "", -1)
	result = strings.Replace(result, ",", ".", -1)
	result = strings.Replace(result, "\n", ".", -1)

	return result
}

func GetMD5Hash(text string) string {
	hasher := md5.New()
	hasher.Write([]byte(text))
	return hex.EncodeToString(hasher.Sum(nil))
}

func GetStudentType(grade string, school string) string {
	if grade == "11" || grade == "12" || grade == "13" || grade == "14" || grade == "15" || grade == "16" {
		return "小学"
	} else if grade == "21" || grade == "22" || grade == "23" || grade == "24" {
		return "初中"
	} else if grade == "31" || grade == "32" || grade == "33" || grade == "34" {
		if _, ok := secondaryVocationalSchoolMap[school]; ok {
			return "中职"
		} else {
			return "高中"
		}
	} else if grade == "41" || grade == "42" || grade == "43" || grade == "44" {
		if _, ok := HigherVocationalSchoolMap[school]; ok {
			return "高职"
		} else {
			return "大学"
		}
	}
	return ""
}

// 所有的cell必须是文本格式，可以通过excel的数据=分列功能完成修改
func ExtractStudent(inFileList []string, outFileName string) {
	studentCodeSet := set.New()
	//classMap := make(map[string]*ClassItem, 0)
	//
	//gradeClassCountMap := make(map[string]*set.Set,0)

	// 打开所有的excel
	xlsxList := make([]*excelize.File, 0)
	for _, fileName := range inFileList {
		temp, err := excelize.OpenFile(fileName)
		if err != nil {
			panic(err.Error())
		}
		xlsxList = append(xlsxList, temp)
	}
	outXlsxFile := excelize.NewFile()
	var lastColumn = 0
	var studentIndex = 0
	for i, xlsxFile := range xlsxList {

		rows := xlsxFile.GetRows("Sheet1")
		for j, row := range rows {
			// 输出表头
			if i == 0 && j == 0{

				for k, colCell := range row {
					//if colCell == "2.0699999999999998" {
					//	for k, colCell := range row {
					//}
					outXlsxFile.SetCellValue("Sheet1", IndexToWord(k) + "1", colCell)
					lastColumn = k +1

				}
				//fmt.Println( CleanField(row[7]))


				outXlsxFile.SetCellValue("Sheet1", IndexToWord(lastColumn) + "1", "学生类型")
				outXlsxFile.SetCellValue("Sheet1", IndexToWord(lastColumn + 1) + "1", "班级编码")
				continue
			}
			studentCode := CleanField(row[5])
			//fmt.Println(studentCode)

			// 重复了
			if studentCodeSet.Has(studentCode) {
				continue
			}
			studentCodeSet.Add(studentCode)
			studentIndex += 1
			for k, colCell := range row {
				outXlsxFile.SetCellValue("Sheet1", IndexToWord(k) + strconv.FormatInt(int64(studentIndex + 1), 10), CleanField(colCell))
			}
			gradeCode := CleanField(row[3])
			schoolName := CleanField(row[2])
			studentType := GetStudentType(gradeCode, schoolName)
			outXlsxFile.SetCellValue("Sheet1", IndexToWord(lastColumn) + strconv.FormatInt(int64(studentIndex + 1), 10), studentType)

			// 生成class code
			classCode := GetMD5Hash(CleanField(row[2] + row[4]))
			outXlsxFile.SetCellValue("Sheet1", IndexToWord(lastColumn + 1) + strconv.FormatInt(int64(studentIndex + 1), 10), classCode)

			//if CleanField(row[7]) == "韩林义" {
			//	fmt.Println("韩林义")
			//
			//	for _, colCell := range row {
			//		fmt.Println(colCell)
			//	}
			//}

		}
	}
	err := outXlsxFile.SaveAs(outFileName)
	if err != nil {
		fmt.Println(err)
	}
}

func IndexToWord(i int) string {
	//asciiNum := 65  // Uppercase A
	character := string(i + 65)
	return character
}